package com.epoint.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Arrays;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import com.epoint.bizlogic.clubInfo.domain.ClubInfo;
import com.epoint.bizlogic.clubInfo.domain.ClubInfoVo;
import com.epoint.utils.JDBCUtil;
import com.epoint.utils.template.insert.MySqlInsert;
import com.epoint.utils.template.query.MySqlQueryAll;
import com.epoint.utils.template.query.MySqlQueryById;
import com.epoint.utils.template.query.MySqlQueryPageBlur;
import com.epoint.utils.template.update.MySqlUpdate;

import cn.hutool.core.util.StrUtil;

public class ClubInfoDao
{

    /**
     * 分页模糊查询信息
     * @param pageIndex 页号
     * @param pageSize 页数
     * @param clubName 俱乐部名称
     * @param clubType 类型
     * @return
     */
    public List<ClubInfoVo> queryClubInfo(Integer pageIndex, Integer pageSize, String clubName, String clubType) {
        Map<String, Object> paramFieldMap = new LinkedHashMap<>();
        StringBuffer sql = new StringBuffer();
        sql.append("select c.* , a.activityid from (select * from clubinfo where 1 = 1");
        if (!StrUtil.isBlankIfStr(clubName)) {
            sql.append(" and clubname like concat('%', ? , '%') ");
            paramFieldMap.put("String", clubName);
        }
        if (!StrUtil.isBlankIfStr(clubType)) {
            sql.append(" and  clubtype = ? ");
            paramFieldMap.put("String", clubType);
        }
        if (pageIndex != null && pageSize != null) {
            sql.append(" limit ?, ? ");
            paramFieldMap.put("Integer_1", pageIndex * pageSize);
            paramFieldMap.put("Integer_2", pageSize);
        }
        
        sql.append(" ) c left join activityinfo a on c.clubid = a.clubid ");
        
        MySqlQueryPageBlur<ClubInfoVo> queryPageBlur = new MySqlQueryPageBlur<>();
        return queryPageBlur.executeSql(sql.toString(), ClubInfoVo.class, paramFieldMap);
    }
    
    /**
     * 新增俱乐部信息
     * @param clubInfo 信息
     * @return 条数
     * 
     */
    public int addClubInfo(ClubInfo clubInfo) {
        String sql = "insert into clubinfo (clubid,clubname,head,clubtype,remain,createtime,remarks) values (?,?,?,?,?,?,?)";
        MySqlInsert<ClubInfo> insert = new MySqlInsert<>();
        return insert.executeSql(sql, clubInfo);
    }
    
    /**
     * 根据id查询俱乐部信息
     * @param clubId id
     * @return 结果
     */
    public ClubInfo queryClubInfoById(String clubId) {
        String sql = "select * from clubinfo where clubid = ?";
        MySqlQueryById<ClubInfo> queryById = new MySqlQueryById<>();
        return queryById.executeSql(sql, clubId, ClubInfo.class);
    }
    
    /**
         * 更新俱乐部信息
     * @param clubInfo 俱乐部信息
     * @return 结果
     */
    public int updateClubInfo(ClubInfo clubInfo) {
        String sql = "update clubinfo set clubname = ? ,head = ? ,clubtype = ? ,remain = ? ,createtime = ? ,remarks = ?  where clubid = ?";
        List<String> sqlFieldList = Arrays.asList("clubname", "head", "clubtype", "remain", "createtime", "remarks", "clubid");
        MySqlUpdate<ClubInfo> update = new MySqlUpdate<>();
        return update.executeSql(sql, sqlFieldList, clubInfo);
    }

    /**
     * 获取全部俱乐部信息
     * @return
     */
    public List<ClubInfoVo> queryAllClubInfo() {
        String sql = "select * from clubinfo";
        MySqlQueryAll<ClubInfoVo> queryAll = new MySqlQueryAll<>();
        return queryAll.executeSql(sql, ClubInfoVo.class);
    }

    /**
     * 级联删除俱乐部信息
     * @param clubIds id
     * @return
     */
    public int deleteClubinfo(String clubIds) {
        String sql1 = "select count(1) from clubinfo where find_in_set (clubid, ?)";
        String sql2 = "select count(1) from activityinfo where find_in_set (clubid, ?)";
        String sql3 = "delete from clubinfo where find_in_set (clubid, ?)";
        String sql4 = "delete from  activityinfo where find_in_set (clubid, ?)";
        
        Connection con = JDBCUtil.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        int t1=0,t2=0,i=0,j=0,result=0;
        
        try {
            con.setAutoCommit(false);
            ps = con.prepareStatement(sql1);
            ps.setString(1, clubIds);
            System.out.println("deleteClubinfo->sql1" + ps);
            rs = ps.executeQuery();
            while(rs.next()) {
                t1 = rs.getInt(1);
            }
            rs.close();
            ps.close();
            
            ps = con.prepareStatement(sql3);
            ps.setString(1, clubIds);
            System.out.println("deleteClubinfo->sql3" + ps);
            i = ps.executeUpdate();
            ps.close();
            
            if (i == t1) {
                con.setAutoCommit(false);
                ps = con.prepareStatement(sql2);
                ps.setString(1, clubIds);
                System.out.println("deleteClubinfo->sql2" + ps);
                rs = ps.executeQuery();
                while(rs.next()) {
                    t2 = rs.getInt(1);
                }
                rs.close();
                ps.close();
                
                ps = con.prepareStatement(sql4);
                ps.setString(1, clubIds);
                System.out.println("deleteClubinfo->sql4" + ps);
                j = ps.executeUpdate();
                ps.close();
            }
            
            if (i == t1 && j == t2) {
                con.commit();
                result = 1;
            } else {
                con.rollback();
                result = 0;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeConnection(rs, ps, con);
        }
        return result;
    }
}
